﻿Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlTypes
Imports System.Diagnostics
Imports System.Globalization
Imports System.Runtime.InteropServices
Imports System.Threading
Imports System.Transactions
Imports System.Workflow.Runtime

Namespace System.Workflow.Runtime.Hosting
    Friend NotInheritable Class PersistenceDBAccessor
        Implements IDisposable
        ' Methods
        Friend Sub New(ByVal dbResourceAllocator As DbResourceAllocator, ByVal enableRetries As Boolean)
            Me.dbResourceAllocator = dbResourceAllocator
            Me.dbRetry = New DbRetry(enableRetries)
            Dim connection As DbConnection = Nothing
            Dim retryCount As Short = 0
        Label_001D:
            Try 
                WorkflowTrace.Host.TraceEvent(TraceEventType.Information, 0, ("SqlWorkflowPersistenceService OpenConnection start: " & DateTime.UtcNow.ToString("G", CultureInfo.InvariantCulture)))
                connection = Me.dbResourceAllocator.OpenNewConnection
                WorkflowTrace.Host.TraceEvent(TraceEventType.Information, 0, ("SqlWorkflowPersistenceService. OpenConnection end: " & DateTime.UtcNow.ToString("G", CultureInfo.InvariantCulture)))
                If ((connection Is Nothing) OrElse (ConnectionState.Open <> connection.State)) Then
                    Throw New InvalidOperationException(ExecutionStringManager.InvalidConnection)
                End If
            Catch exception As Exception
                WorkflowTrace.Host.TraceEvent(TraceEventType.Error, 0, ("SqlWorkflowPersistenceService caught exception from OpenConnection: " & exception.ToString))
                If Not Me.dbRetry.TryDoRetry((retryCount)) Then
                    Throw
                End If
                WorkflowTrace.Host.TraceEvent(TraceEventType.Information, 0, "SqlWorkflowPersistenceService retrying.")
                goto Label_001D
            End Try
            Me.connection = connection
            Me.needToCloseConnection = True
        End Sub

        Friend Sub New(ByVal dbResourceAllocator As DbResourceAllocator, ByVal transaction As Transaction, ByVal transactionService As WorkflowCommitWorkBatchService)
            Me.dbResourceAllocator = dbResourceAllocator
            Me.localTransaction = DbResourceAllocator.GetLocalTransaction(transactionService, transaction)
            Me.connection = Me.dbResourceAllocator.GetEnlistedConnection(transactionService, transaction, Me.needToCloseConnection)
            Me.dbRetry = New DbRetry(False)
        End Sub

        Public Sub ActivationComplete(ByVal instanceId As Guid, ByVal ownerId As Guid)
            Dim command As DbCommand = Me.NewStoredProcCommand("UnlockInstanceState")
            command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("@uidInstanceID", instanceId))
            command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("@ownerID", Me.DbOwnerId(ownerId)))
            WorkflowTrace.Host.TraceEvent(TraceEventType.Information, 0, "SqlWorkflowPersistenceService({0}): unlocking instance: {1}, database: {2}", New Object() { ownerId.ToString, instanceId.ToString, Me.connection.Database })
            command.ExecuteNonQuery
        End Sub

        Private Shared Sub CheckOwnershipResult(ByVal command As DbCommand)
            Dim parameter As DbParameter = command.Parameters.Item("@result")
            If (((Not parameter Is Nothing) AndAlso (Not parameter.Value Is Nothing)) AndAlso (CInt(parameter.Value) = -2)) Then
                If command.Parameters.Contains("@currentOwnerID") Then
                    Dim empty As Guid = Guid.Empty
                    If TypeOf command.Parameters.Item("@currentOwnerID").Value Is Guid Then
                        empty = DirectCast(command.Parameters.Item("@currentOwnerID").Value, Guid)
                    End If
                    Dim guid2 As Guid = DirectCast(command.Parameters.Item("@ownerID").Value, Guid)
                    Dim guid3 As Guid = DirectCast(command.Parameters.Item("@uidInstanceID").Value, Guid)
                    WorkflowTrace.Host.TraceEvent(TraceEventType.Information, 0, "SqlWorkflowPersistenceService({0}): owership violation with {1} on instance {2}", New Object() { guid2.ToString, empty.ToString, guid3 })
                End If
                Dim parameter2 As DbParameter = command.Parameters.Item("@uidInstanceID")
                Throw New WorkflowOwnershipException(DirectCast(parameter2.Value, Guid))
            End If
        End Sub

        Private Function DbOwnerId(ByVal ownerId As Guid) As Object
            If (ownerId = Guid.Empty) Then
                Return Nothing
            End If
            Return ownerId
        End Function

        Public Sub Dispose()
            If Me.needToCloseConnection Then
                Me.connection.Dispose
            End If
        End Sub

        Public Sub InsertCompletedScope(ByVal instanceId As Guid, ByVal scopeId As Guid, ByVal state As Byte())
            Dim command As DbCommand = Me.NewStoredProcCommand("InsertCompletedScope")
            command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("instanceID", instanceId))
            command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("completedScopeID", scopeId))
            command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("state", state))
            command.ExecuteNonQuery
        End Sub

        Public Sub InsertInstanceState(ByVal item As PendingWorkItem, ByVal ownerId As Guid, ByVal ownedUntil As DateTime)
            Dim command As DbCommand = Me.NewStoredProcCommand("InsertInstanceState")
            command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("@uidInstanceID", item.InstanceId))
            command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("@state", item.SerializedActivity))
            command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("@status", item.Status))
            command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("@unlocked", item.Unlocked))
            command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("@blocked", item.Blocked))
            command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("@info", item.Info))
            command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("@ownedUntil", If((ownedUntil = DateTime.MaxValue), SqlDateTime.MaxValue, ownedUntil)))
            command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("@ownerID", Me.DbOwnerId(ownerId)))
            command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("@nextTimer", item.NextTimer))
            Dim parameter As DbParameter = Me.dbResourceAllocator.NewDbParameter
            parameter.ParameterName = "@result"
            parameter.DbType = DbType.Int32
            parameter.Value = 0
            parameter.Direction = ParameterDirection.InputOutput
            command.Parameters.Add(parameter)
            Dim parameter2 As DbParameter = Me.dbResourceAllocator.NewDbParameter
            parameter2.ParameterName = "@currentOwnerID"
            parameter2.DbType = DbType.Guid
            parameter2.Value = Guid.Empty
            parameter2.Direction = ParameterDirection.InputOutput
            command.Parameters.Add(parameter2)
            WorkflowTrace.Host.TraceEvent(TraceEventType.Information, 0, "SqlWorkflowPersistenceService({0}): inserting instance: {1}, unlocking: {2} database: {3}", New Object() { ownerId.ToString, item.InstanceId.ToString, item.Unlocked.ToString, Me.connection.Database })
            command.ExecuteNonQuery
            PersistenceDBAccessor.CheckOwnershipResult(command)
        End Sub

        Private Function NewStoredProcCommand(ByVal commandText As String) As DbCommand
            Dim command As DbCommand = DbResourceAllocator.NewCommand(commandText, Me.connection, Me.localTransaction)
            command.CommandType = CommandType.StoredProcedure
            Return command
        End Function

        Private Function ResetConnection() As DbConnection
            If (Not Me.localTransaction Is Nothing) Then
                Throw New InvalidOperationException(ExecutionStringManager.InvalidOpConnectionReset)
            End If
            If Not Me.needToCloseConnection Then
                Throw New InvalidOperationException(ExecutionStringManager.InvalidOpConnectionNotLocal)
            End If
            If ((Not Me.connection Is Nothing) AndAlso (Me.connection.State <> ConnectionState.Closed)) Then
                Me.connection.Close
            End If
            Me.connection.Dispose
            Me.connection = Me.dbResourceAllocator.OpenNewConnection
            Return Me.connection
        End Function

        Friend Function RetrieveAllInstanceDescriptions() As IEnumerable([Of] SqlPersistenceWorkflowInstanceDescription)
            Dim list As New List([Of] SqlPersistenceWorkflowInstanceDescription)
            Dim reader As DbDataReader = Nothing
            Try 
                reader = Me.NewStoredProcCommand("RetrieveAllInstanceDescriptions").ExecuteReader(CommandBehavior.CloseConnection)
                Do While reader.Read
                    list.Add(New SqlPersistenceWorkflowInstanceDescription(reader.GetGuid(0), DirectCast(reader.GetInt32(1), WorkflowStatus), (reader.GetInt32(2) = 1), reader.GetString(3), reader.GetDateTime(4)))
                Loop
            Finally
                If (Not reader Is Nothing) Then
                    reader.Close
                End If
            End Try
            Return list
        End Function

        Public Function RetrieveCompletedScope(ByVal scopeId As Guid) As Byte()
            Dim retryCount As Short = 0
            Dim buffer As Byte() = Nothing
        Label_0004:
            Try 
                If ((Me.connection Is Nothing) OrElse (ConnectionState.Open <> Me.connection.State)) Then
                    Me.ResetConnection
                End If
                Dim command As DbCommand = Me.NewStoredProcCommand("RetrieveCompletedScope")
                command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("@completedScopeID", scopeId))
                Dim parameter As DbParameter = Me.dbResourceAllocator.NewDbParameter
                parameter.ParameterName = "@result"
                parameter.DbType = DbType.Int32
                parameter.Value = 0
                parameter.Direction = ParameterDirection.InputOutput
                command.Parameters.Add(parameter)
                buffer = PersistenceDBAccessor.RetrieveStateFromDB(command, False, WorkflowEnvironment.WorkflowInstanceId)
            Catch exception As Exception
                WorkflowTrace.Host.TraceEvent(TraceEventType.Error, 0, ("SqlWorkflowPersistenceService.RetrieveCompletedScope caught exception: " & exception.ToString))
                If Me.dbRetry.TryDoRetry((retryCount)) Then
                    WorkflowTrace.Host.TraceEvent(TraceEventType.Information, 0, "SqlWorkflowPersistenceService.RetrieveCompletedScope retrying.")
                    goto Label_0004
                End If
                If Not TypeOf exception Is RetryReadException Then
                    Throw
                End If
                retryCount = CShort((retryCount + 1))
                If (retryCount < 10) Then
                    goto Label_0004
                End If
            End Try
            If ((buffer Is Nothing) OrElse (buffer.Length = 0)) Then
                Throw New InvalidOperationException(String.Format(Thread.CurrentThread.CurrentCulture, ExecutionStringManager.CompletedScopeNotFound, New Object() { scopeId }))
            End If
            Return buffer
        End Function

        Public Function RetrieveExpiredTimerIds(ByVal ownerId As Guid, ByVal ownedUntil As DateTime) As IList([Of] Guid)
            Dim list As List([Of] Guid) = Nothing
            Dim reader As DbDataReader = Nothing
            Dim retryCount As Short = 0
        Label_0006:
            Try 
                If ((Me.connection Is Nothing) OrElse (ConnectionState.Open <> Me.connection.State)) Then
                    Me.ResetConnection
                End If
                Dim command As DbCommand = Me.NewStoredProcCommand("RetrieveExpiredTimerIds")
                command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("@ownedUntil", If((ownedUntil = DateTime.MaxValue), SqlDateTime.MaxValue, ownedUntil)))
                command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("@ownerID", Me.DbOwnerId(ownerId)))
                command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("@now", DateTime.UtcNow))
                WorkflowTrace.Host.TraceEvent(TraceEventType.Information, 0, ("SqlWorkflowPersistenceService.RetrieveExpiredTimerIds ExecuteReader start: " & DateTime.UtcNow.ToString("G", CultureInfo.InvariantCulture)))
                reader = command.ExecuteReader(CommandBehavior.CloseConnection)
                WorkflowTrace.Host.TraceEvent(TraceEventType.Information, 0, ("SqlWorkflowPersistenceService.RetrieveExpiredTimerIds ExecuteReader end: " & DateTime.UtcNow.ToString("G", CultureInfo.InvariantCulture)))
                list = New List([Of] Guid)
                Do While reader.Read
                    list.Add(reader.GetGuid(0))
                Loop
            Catch exception As Exception
                WorkflowTrace.Host.TraceEvent(TraceEventType.Error, 0, ("SqlWorkflowPersistenceService.RetrieveExpiredTimerIds caught exception from ExecuteReader: " & exception.ToString))
                If Not Me.dbRetry.TryDoRetry((retryCount)) Then
                    Throw
                End If
                WorkflowTrace.Host.TraceEvent(TraceEventType.Information, 0, "SqlWorkflowPersistenceService.RetrieveExpiredTimerIds retrying.")
                goto Label_0006
            Finally
                If (Not reader Is Nothing) Then
                    reader.Close
                End If
            End Try
            Return list
        End Function

        Public Function RetrieveInstanceState(ByVal instanceStateId As Guid, ByVal ownerId As Guid, ByVal timeout As DateTime) As Byte()
            Dim retryCount As Short = 0
            Dim buffer As Byte() = Nothing
        Label_0004:
            Try 
                If ((Me.connection Is Nothing) OrElse (ConnectionState.Open <> Me.connection.State)) Then
                    Me.ResetConnection
                End If
                Dim command As DbCommand = Me.NewStoredProcCommand("RetrieveInstanceState")
                command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("@uidInstanceID", instanceStateId))
                command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("@ownerID", Me.DbOwnerId(ownerId)))
                command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("@ownedUntil", If((timeout = DateTime.MaxValue), SqlDateTime.MaxValue, timeout)))
                Dim parameter As DbParameter = Me.dbResourceAllocator.NewDbParameter
                parameter.ParameterName = "@result"
                parameter.DbType = DbType.Int32
                parameter.Value = 0
                parameter.Direction = ParameterDirection.InputOutput
                command.Parameters.Add(parameter)
                Dim parameter2 As DbParameter = Me.dbResourceAllocator.NewDbParameter
                parameter2.ParameterName = "@currentOwnerID"
                parameter2.DbType = DbType.Guid
                parameter2.Value = Guid.Empty
                parameter2.Direction = ParameterDirection.InputOutput
                command.Parameters.Add(parameter2)
                WorkflowTrace.Host.TraceEvent(TraceEventType.Information, 0, "SqlWorkflowPersistenceService({0}): retreiving instance: {1}, database: {2}", New Object() { ownerId.ToString, instanceStateId.ToString, Me.connection.Database })
                buffer = PersistenceDBAccessor.RetrieveStateFromDB(command, True, instanceStateId)
            Catch exception As Exception
                WorkflowTrace.Host.TraceEvent(TraceEventType.Error, 0, ("SqlWorkflowPersistenceService.RetrieveInstanceState caught exception: " & exception.ToString))
                If Me.dbRetry.TryDoRetry((retryCount)) Then
                    WorkflowTrace.Host.TraceEvent(TraceEventType.Information, 0, "SqlWorkflowPersistenceService.RetrieveInstanceState retrying.")
                    goto Label_0004
                End If
                If Not TypeOf exception Is RetryReadException Then
                    Throw
                End If
                retryCount = CShort((retryCount + 1))
                If (retryCount < 10) Then
                    goto Label_0004
                End If
            End Try
            If ((Not buffer Is Nothing) AndAlso (buffer.Length <> 0)) Then
                Return buffer
            End If
            Dim exception2 As Exception = New InvalidOperationException(String.Format(Thread.CurrentThread.CurrentCulture, ExecutionStringManager.InstanceNotFound, New Object() { instanceStateId }))
            exception2.Data.Item("WorkflowNotFound") = True
            Throw exception2
        End Function

        Public Function RetrieveNonblockingInstanceStateIds(ByVal ownerId As Guid, ByVal ownedUntil As DateTime) As IList([Of] Guid)
            Dim list As List([Of] Guid) = Nothing
            Dim reader As DbDataReader = Nothing
            Dim retryCount As Short = 0
        Label_0006:
            Try 
                If ((Me.connection Is Nothing) OrElse (ConnectionState.Open <> Me.connection.State)) Then
                    Me.ResetConnection
                End If
                Dim command As DbCommand = Me.NewStoredProcCommand("RetrieveNonblockingInstanceStateIds")
                command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("@ownedUntil", If((ownedUntil = DateTime.MaxValue), SqlDateTime.MaxValue, ownedUntil)))
                command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("@ownerID", Me.DbOwnerId(ownerId)))
                command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("@now", DateTime.UtcNow))
                WorkflowTrace.Host.TraceEvent(TraceEventType.Information, 0, ("SqlWorkflowPersistenceService.RetrieveNonblockingInstanceStateIds ExecuteReader start: " & DateTime.UtcNow.ToString("G", CultureInfo.InvariantCulture)))
                reader = command.ExecuteReader(CommandBehavior.CloseConnection)
                WorkflowTrace.Host.TraceEvent(TraceEventType.Information, 0, ("SqlWorkflowPersistenceService.RetrieveNonblockingInstanceStateIds ExecuteReader end: " & DateTime.UtcNow.ToString("G", CultureInfo.InvariantCulture)))
                list = New List([Of] Guid)
                Do While reader.Read
                    list.Add(reader.GetGuid(0))
                Loop
            Catch exception As Exception
                WorkflowTrace.Host.TraceEvent(TraceEventType.Error, 0, ("SqlWorkflowPersistenceService.RetrieveNonblockingInstanceStateIds caught exception from ExecuteReader: " & exception.ToString))
                If Not Me.dbRetry.TryDoRetry((retryCount)) Then
                    Throw
                End If
                WorkflowTrace.Host.TraceEvent(TraceEventType.Information, 0, "SqlWorkflowPersistenceService.RetrieveNonblockingInstanceStateIds retrying.")
                goto Label_0006
            Finally
                If (Not reader Is Nothing) Then
                    reader.Close
                End If
            End Try
            Return list
        End Function

        Private Shared Function RetrieveStateFromDB(ByVal command As DbCommand, ByVal checkOwnership As Boolean, ByVal instanceId As Guid) As Byte()
            Dim reader As DbDataReader = Nothing
            Dim buffer As Byte() = Nothing
            Try 
                WorkflowTrace.Host.TraceEvent(TraceEventType.Information, 0, "SqlWorkflowPersistenceService.RetrieveStateFromDB {0} ExecuteReader start: {1}", New Object() { instanceId, DateTime.UtcNow.ToString("G", CultureInfo.InvariantCulture) })
                reader = command.ExecuteReader
                WorkflowTrace.Host.TraceEvent(TraceEventType.Information, 0, "SqlWorkflowPersistenceService.RetrieveStateFromDB {0} ExecuteReader end: {1}", New Object() { instanceId, DateTime.UtcNow.ToString("G", CultureInfo.InvariantCulture) })
                If reader.Read Then
                    buffer = DirectCast(reader.GetValue(0), Byte())
                Else
                    Dim parameter As DbParameter = command.Parameters.Item("@result")
                    If ((parameter Is Nothing) OrElse (parameter.Value Is Nothing)) Then
                        WorkflowTrace.Host.TraceEvent(TraceEventType.Information, 0, "SqlWorkflowPersistenceService.RetrieveStateFromDB Failed to read results {0}", New Object() { instanceId })
                    ElseIf (CInt(parameter.Value) > 0) Then
                        WorkflowTrace.Host.TraceEvent(TraceEventType.Error, 0, "SqlWorkflowPersistenceService.RetrieveStateFromDB Failed to read results {1}, @result == {0}", New Object() { CInt(parameter.Value), instanceId })
                        Throw New RetryReadException
                    End If
                End If
            Finally
                If (Not reader Is Nothing) Then
                    reader.Close
                End If
            End Try
            If checkOwnership Then
                PersistenceDBAccessor.CheckOwnershipResult(command)
            End If
            Return buffer
        End Function

        Public Function TryRetrieveANonblockingInstanceStateId(ByVal ownerId As Guid, ByVal ownedUntil As DateTime, <Out> ByRef instanceId As Guid) As Boolean
            Dim flag As Boolean
            Dim retryCount As Short = 0
        Label_0002:
            Try 
                If ((Me.connection Is Nothing) OrElse (ConnectionState.Open <> Me.connection.State)) Then
                    Me.ResetConnection
                End If
                Dim command As DbCommand = Me.NewStoredProcCommand("RetrieveANonblockingInstanceStateId")
                command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("@ownedUntil", If((ownedUntil = DateTime.MaxValue), SqlDateTime.MaxValue, ownedUntil)))
                command.Parameters.Add(Me.dbResourceAllocator.NewDbParameter("@ownerID", Me.DbOwnerId(ownerId)))
                Dim parameter As DbParameter = Me.dbResourceAllocator.NewDbParameter
                parameter.ParameterName = "@uidInstanceID"
                parameter.DbType = DbType.Guid
                parameter.Value = Nothing
                parameter.Direction = ParameterDirection.InputOutput
                command.Parameters.Add(parameter)
                Dim parameter2 As DbParameter = Me.dbResourceAllocator.NewDbParameter
                parameter2.ParameterName = "@found"
                parameter2.DbType = DbType.Boolean
                parameter2.Value = Nothing
                parameter2.Direction = ParameterDirection.Output
                command.Parameters.Add(parameter2)
                WorkflowTrace.Host.TraceEvent(TraceEventType.Information, 0, ("SqlWorkflowPersistenceService.TryRetrieveANonblockingInstanceStateId ExecuteNonQuery start: " & DateTime.UtcNow.ToString("G", CultureInfo.InvariantCulture)))
                command.ExecuteNonQuery
                WorkflowTrace.Host.TraceEvent(TraceEventType.Information, 0, ("SqlWorkflowPersistenceService.TryRetrieveANonblockingInstanceStateId ExecuteNonQuery end: " & DateTime.UtcNow.ToString("G", CultureInfo.InvariantCulture)))
                If ((Not parameter2.Value Is Nothing) AndAlso CBool(parameter2.Value)) Then
                    instanceId = DirectCast(parameter.Value, Guid)
                    Return True
                End If
                instanceId = Guid.Empty
                flag = False
            Catch exception As Exception
                WorkflowTrace.Host.TraceEvent(TraceEventType.Error, 0, ("SqlWorkflowPersistenceService.TryRetrieveANonblockingInstanceStateId caught exception from ExecuteNonQuery: " & exception.ToString))
                If Not Me.dbRetry.TryDoRetry((retryCount)) Then
                    Throw
                End If
                WorkflowTrace.Host.TraceEvent(TraceEventType.Information, 0, "SqlWorkflowPersistenceService.TryRetrieveANonblockingInstanceStateId retrying.")
                goto Label_0002
            End Try
            Return flag
        End Function


        ' Fields
        Private connection As DbConnection
        Private dbResourceAllocator As DbResourceAllocator
        Private dbRetry As DbRetry
        Private localTransaction As DbTransaction
        Private needToCloseConnection As Boolean

        ' Nested Types
        Private Class RetryReadException
            Inherits Exception
        End Class
    End Class
End Namespace

